{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Helpdesk Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "shift = pd.read_sql_table('Shift', engine)\n",
    "staff = pd.read_sql_table('Staff', engine)\n",
    "issue = pd.read_sql_table('Issue', engine)\n",
    "shift_type = pd.read_sql_table('Shift_type', engine)\n",
    "level = pd.read_sql_table('Level', engine)\n",
    "customer = pd.read_sql_table('Customer', engine)\n",
    "caller = pd.read_sql_table('Caller', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "List the Company name and the number of calls for those companies with more than 18 calls.\n",
    "\n",
    "```\n",
    "+------------------+----+\n",
    "| Company_name     | cc |\n",
    "+------------------+----+\n",
    "| Gimmick Inc.     | 22 |\n",
    "| Hamming Services | 19 |\n",
    "| High and Co.     | 20 |\n",
    "+------------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company_name</th>\n",
       "      <th>Call_ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Gimmick Inc.</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Hamming Services</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>High and Co.</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Company_name  Call_ref\n",
       "16      Gimmick Inc.        22\n",
       "18  Hamming Services        19\n",
       "20      High and Co.        20"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(issue.merge(caller, on='Caller_id')\n",
    " .merge(customer, on='Company_ref')\n",
    " [['Company_name', 'Call_ref']]\n",
    " .groupby('Company_name')\n",
    " .count().reset_index()\n",
    " .query('Call_ref>18'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Find the callers who have never made a call. Show first name and last name\n",
    "\n",
    "```\n",
    "+------------+-----------+\n",
    "| first_name | last_name |\n",
    "+------------+-----------+\n",
    "| David      | Jackson   |\n",
    "| Ethan      | Phillips  |\n",
    "+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First_name</th>\n",
       "      <th>Last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>David</td>\n",
       "      <td>Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Ethan</td>\n",
       "      <td>Phillips</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    First_name Last_name\n",
       "10       David   Jackson\n",
       "126      Ethan  Phillips"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(caller[~caller['Caller_id'].isin(issue['Caller_id'])]\n",
    " [['First_name', 'Last_name']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5\n",
    "\n",
    "```\n",
    "+--------------------+------------+-----------+----+\n",
    "| Company_name       | first_name | last_name | nc |\n",
    "+--------------------+------------+-----------+----+\n",
    "| Pitiable Shipping  | Ethan      | McConnell |  4 |\n",
    "| Rajab Group        | Emily      | Cooper    |  4 |\n",
    "| Somebody Logistics | Ethan      | Phillips  |  2 |\n",
    "+--------------------+------------+-----------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company_name</th>\n",
       "      <th>First_name</th>\n",
       "      <th>Last_name</th>\n",
       "      <th>Call_ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Somebody Logistics</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Pitiable Shipping</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Rajab Group</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Company_name  First_name  Last_name  Call_ref\n",
       "4   Somebody Logistics           2          2         2\n",
       "28   Pitiable Shipping           4          4         4\n",
       "43         Rajab Group           4          4         4"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(issue.merge(caller[['Caller_id', 'Company_ref']], on='Caller_id')\n",
    " .merge(customer, on='Company_ref')\n",
    " .merge(caller[['Caller_id', 'First_name', 'Last_name']], \n",
    "        left_on='Contact_id', right_on='Caller_id')\n",
    " [['Company_ref', 'Company_name', 'First_name', 'Last_name', 'Call_ref']]\n",
    " .groupby(['Company_ref', 'Company_name'])\n",
    " .count().reset_index()\n",
    " .query('Call_ref<5')\n",
    " [['Company_name', 'First_name', 'Last_name', 'Call_ref']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "For each shift show the number of staff assigned. Beware that some roles may be NULL and that the same person might have been assigned to multiple roles (The roles are 'Manager', 'Operator', 'Engineer1', 'Engineer2').\n",
    "\n",
    "```\n",
    "+------------+------------+----+\n",
    "| Shift_date | Shift_type | cw |\n",
    "+------------+------------+----+\n",
    "| 2017-08-12 | Early      |  4 |\n",
    "| 2017-08-12 | Late       |  4 |\n",
    "| 2017-08-13 | Early      |  3 |\n",
    "| 2017-08-13 | Late       |  2 |\n",
    "| 2017-08-14 | Early      |  4 |\n",
    "| 2017-08-14 | Late       |  4 |\n",
    "| 2017-08-15 | Early      |  4 |\n",
    "| 2017-08-15 | Late       |  4 |\n",
    "| 2017-08-16 | Early      |  4 |\n",
    "| 2017-08-16 | Late       |  4 |\n",
    "+------------+------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Shift_date</th>\n",
       "      <th>Shift_type</th>\n",
       "      <th>role</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-08-12</td>\n",
       "      <td>Early</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-08-12</td>\n",
       "      <td>Late</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-08-13</td>\n",
       "      <td>Early</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-08-13</td>\n",
       "      <td>Late</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-08-14</td>\n",
       "      <td>Early</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017-08-14</td>\n",
       "      <td>Late</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>Early</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>Late</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>Early</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>Late</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Shift_date Shift_type  role\n",
       "0 2017-08-12      Early     4\n",
       "1 2017-08-12       Late     4\n",
       "2 2017-08-13      Early     3\n",
       "3 2017-08-13       Late     2\n",
       "4 2017-08-14      Early     4\n",
       "5 2017-08-14       Late     4\n",
       "6 2017-08-15      Early     4\n",
       "7 2017-08-15       Late     4\n",
       "8 2017-08-16      Early     4\n",
       "9 2017-08-16       Late     4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(shift[['Shift_date', 'Shift_type', 'Manager', \n",
    "        'Operator', 'Engineer1', 'Engineer2']]\n",
    " .set_index(['Shift_date', 'Shift_type'])\n",
    " .stack().reset_index()\n",
    " .rename(columns={'level_2': 'role', 0: 'p'})\n",
    " .drop_duplicates(subset=['Shift_date', 'Shift_type', 'p'])\n",
    " [['Shift_date', 'Shift_type', 'role']]\n",
    " .groupby(['Shift_date', 'Shift_type'])\n",
    " .count().reset_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Caller 'Harry' claims that the operator who took his most recent call was abusive and insulting. Find out who took the call (full name) and when.\n",
    "\n",
    "```\n",
    "+------------+-----------+---------------------+\n",
    "| first_name | last_name | call_date           |\n",
    "+------------+-----------+---------------------+\n",
    "| Emily      | Best      | 2017-08-16 10:25:00 |\n",
    "+------------+-----------+---------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First_name</th>\n",
       "      <th>Last_name</th>\n",
       "      <th>Call_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Emily</td>\n",
       "      <td>Best</td>\n",
       "      <td>2017-08-16 10:25:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  First_name Last_name           Call_date\n",
       "3      Emily      Best 2017-08-16 10:25:00"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(caller.loc[caller['First_name']=='Harry', ['Caller_id']]\n",
    " .merge(issue[['Taken_by', 'Caller_id', 'Call_date']], on='Caller_id')\n",
    " .merge(staff, left_on='Taken_by', right_on='Staff_code')\n",
    " [['First_name', 'Last_name', 'Call_date']]\n",
    " .sort_values('Call_date', ascending=False)\n",
    " .head(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
